package com.bw.mapper;

import com.bw.bean.vo;
import com.bw.bean.vo1;
import com.bw.bean.vo2;
import com.bw.bean.vo3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface vomapper {

    @Select("select s.name, sum(p.reduce_amount) `value`  from order_info oi\n" +
            "                                                      left join  promotion p on oi.promotion_id = p.id\n" +
            "                                                      left join order_detail od on oi.id = od.order_info_id\n" +
            "                                                      left join product_sku ps on od.product_sku_id = ps.id\n" +
            "                                                      left join product_spu s on ps.product_spu_id = s.id\n" +
            "group by s.name having s.name is not null order by `value` desc limit 10")
    List<vo> cx1();
@Select("select  type,\n" +
        "       sum(original_amount) 'amount1' ,\n" +
        "       count(oi.id) 'count1',\n" +
        "        count(distinct oi.customer_id)'count2',\n" +
        "      sum(actual_amount)  'amount2',\n" +
        "        sum(amount)'amount3'\n" +
        "        from  shop s\n" +
        "left join order_info oi on s.id = oi.shop_id\n" +
        "left join payment p on s.id = p.shop_id and status = 5\n" +
        "group by  type;")
    List<vo1> cx2();
@Select("select name,\n" +
        "       avg(rating)'pf',\n" +
        "       count(s.id)'count1',\n" +
        "       sum(case when rating= 5 then 1 else 0 end )'count2',\n" +
        "       sum(case when rating= 5 then 1 else 0 end )/count(s.id)'lv'\n" +
        "       from  shop s\n" +
        "left join order_info oi on s.id = oi.shop_id\n" +
        "group by name;")
    List<vo2> cx3();
@Select("select  s.name,\n" +
        "       sum(reduce_amount) 'amount1',\n" +
        "        rank() over (order by sum(reduce_amount)) 'num1',\n" +
        "     count(p.id)    'num2'\n" +
        "        from  shop s\n" +
        "left join promotion p on s.create_day = p.create_day\n" +
        "left join order_info oi on s.id = oi.shop_id\n" +
        "group by s.name;")
    List<vo3> cx4();
}
